﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;

namespace MySearch
{
    internal static class UserInfoDAO
    {
        private static AdoTemplate template = AppConfigurer.AdoTemplate;

        #region get and list

        internal static AgentTeam GetAgentTeam(string teamCode)
        {
            AgentTeam agentTeam = null;
            string strSel = "select team_code, team_name, office, is_system, status from Agent_Team where team_code = @teamCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strCode = dr.GetString(0);
                string strName = dr.GetString(1);
                string strOffice = dr.GetString(2);
                string strIsSystem = dr.GetString(3);
                string status = dr.GetString(4);

                bool isSystem = GetIsSystem(strIsSystem);
                TeamStatus enumStatus = GetStatus(status);

                agentTeam = new AgentTeam(strCode, strName, strOffice, isSystem, enumStatus);
            }, p);

            return agentTeam;
        }

        internal static IList<AgentTeam> ListAgentTeam()
        {
            IList<AgentTeam> lstRet = new List<AgentTeam>();
            string strSel = "select team_code, team_name, office, is_system, status from Agent_Team ";

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strCode = dr.GetString(0);
                string strName = dr.GetString(1);
                string strOffice = dr.GetString(2);
                string strIsSystem = dr.GetString(3);
                string status = dr.GetString(4);

                bool isSystem = GetIsSystem(strIsSystem);
                TeamStatus enumStatus = GetStatus(status);

                AgentTeam agentTeam = new AgentTeam(strCode, strName, strOffice, isSystem, enumStatus);
                lstRet.Add(agentTeam);
            });

            return lstRet;
        }

        internal static bool CheckTeamNameCode(string teamCode)
        {
            string strSel = "select top 1 team_code, team_name, office, is_system, status from Agent_Team where team_code = @teamCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;

            string strCode = null;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                strCode = dr.GetString(0);

            }, p);

            return !string.IsNullOrEmpty(strCode);
        }

        internal static TeamRole? GetTeamRole(string staffNo, string teamCode)
        {
            string strRole = null;
            TeamRole role;
            string strSel = "select team_role from Agent_Team_Member where Staff_No=@staffNo and Team_code=@teamCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("staffNo", DbType.String).Value = staffNo;
            p.Add("teamCode", DbType.String).Value = teamCode;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                strRole = dr.GetString(0);

            }, p);

            if (!string.IsNullOrEmpty(strRole) && Enum.TryParse<TeamRole>(strRole, out role))
                return role;

            return null;
        }

        internal static bool CheckTeamLeader(string teamCode)
        {
            string strSel = "select top 1 staff_no, team_code, team_role from Agent_Team_Member where team_code = @teamCode and Team_Role=@teamRole ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;
            p.Add("teamRole", DbType.String).Value = TeamRole.Leader.ToString();

            string str = null;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                str = dr.GetString(0);

            }, p);

            return !string.IsNullOrEmpty(str);
        }

        // return <teamCode,TeamRole>
        internal static IDictionary<string, TeamRole> ListMyTeam(string staffNo)
        {
            IDictionary<string, TeamRole> dicRet = new Dictionary<string, TeamRole>();

            string strSel = "select team_code, team_role from Agent_Team_Member where staff_no=@staffNo ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("staffNo", DbType.String).Value = staffNo;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string teamCode = dr.GetString(0);
                string role = dr.GetString(1);
                TeamRole teamRole;

                if (!string.IsNullOrEmpty(teamCode) && !string.IsNullOrEmpty(role) && !dicRet.ContainsKey(teamCode) && Enum.TryParse<TeamRole>(role, out teamRole))
                {
                    dicRet.Add(teamCode, teamRole);
                }

            }, p);

            return dicRet;
        }

        // return <staffNo,TeamRole>
        internal static IDictionary<string, TeamRole> ListAgentTeamMember(string teamCode)
        {
            IDictionary<string, TeamRole> dicRet = new Dictionary<string, TeamRole>();

            string strSel = "select staff_no, team_role from Agent_Team_Member where team_code=@teamCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string staffNo = dr.GetString(0);
                string role = dr.GetString(1);
                TeamRole teamRole;

                if (!string.IsNullOrEmpty(staffNo) && !string.IsNullOrEmpty(role) && !dicRet.ContainsKey(staffNo) && Enum.TryParse<TeamRole>(role, out teamRole))
                {
                    dicRet.Add(staffNo, teamRole);
                }

            }, p);

            return dicRet;
        }

        internal static IList<string> ListAgentTeamServiceType(string teamCode)
        {
            IList<string> lstRet = new List<string>();
            string strSel = "select type_code from Agent_Team_Service_Type where team_code=@teamCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strCode = dr.GetString(0);

                if (!string.IsNullOrEmpty(strCode))
                    lstRet.Add(strCode);
            }, p);

            return lstRet;
        }

        #endregion

        internal static void CreateAgentTeam(string teamCode, string teamName, string office, bool isSystem, TeamStatus status)
        {
            string strIns = "insert into Agent_Team(team_code, team_name, office, is_system, status) "
               + " values(@teamCode, @teamName, @office, @isSystem, @status) ";

            string strIsSystem = GetStrIsSystem(isSystem);

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;
            p.Add("teamName", DbType.String).Value = teamName;
            p.Add("office", DbType.String).Value = office;
            p.Add("isSystem", DbType.String).Value = strIsSystem;
            p.Add("status", DbType.String).Value = status.ToString();

            template.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void DeteleAgentTeam(string teamCode)
        {
            IDbParameters para = template.CreateDbParameters();
            para.Add("teamCode", DbType.String).Value = teamCode;

            string strDel = " delete from Agent_Team where team_code=@teamCode ";
            template.ExecuteScalar(CommandType.Text, strDel, para);
        }

        internal static void UpdateAgentTeam(string teamCode, string teamName, string office, bool isSystem, TeamStatus status)
        {
            string strUpt = "update Agent_Team set team_name=@teamName, office=@office, is_system=@isSystem, status=@status "
               + " where team_code=@teamCode ";

            string strIsSystem = GetStrIsSystem(isSystem);

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamName", DbType.String).Value = teamName;
            p.Add("office", DbType.String).Value = office;
            p.Add("isSystem", DbType.String).Value = strIsSystem;
            p.Add("status", DbType.String).Value = status.ToString();
            p.Add("teamCode", DbType.String).Value = teamCode;

            template.ExecuteNonQuery(CommandType.Text, strUpt, p);
        }

        internal static void CreateTeamMember(string teamCode, string staffNo, TeamRole teamRole)
        {
            string strIns = "insert into Agent_Team_Member(staff_no, team_code, team_role) "
               + " values(@staffNo, @teamCode, @teamRole) ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("staffNo", DbType.String).Value = staffNo;
            p.Add("teamCode", DbType.String).Value = teamCode;
            p.Add("teamRole", DbType.String).Value = teamRole.ToString();


            template.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void DeteleTeamMember(string teamCode, string staffNo)
        {
            IDbParameters para = template.CreateDbParameters();
            para.Add("teamCode", DbType.String).Value = teamCode;
            para.Add("staffNo", DbType.String).Value = staffNo;

            string strDel = " delete from Agent_Team_Member where  staff_no=@staffNo and team_code=@teamCode ";
            template.ExecuteScalar(CommandType.Text, strDel, para);
        }

        internal static void UpdateTeamRole(string staffNo, string teamCode, TeamRole teamRole)
        {
            string strUpt = "update Agent_Team_Member set team_role=@teamRole "
               + " where staff_no=@staffNo and team_code=@teamCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("staffNo", DbType.String).Value = staffNo;
            p.Add("teamCode", DbType.String).Value = teamCode;
            p.Add("teamRole", DbType.String).Value = teamRole.ToString();

            template.ExecuteNonQuery(CommandType.Text, strUpt, p);
        }

        internal static void CreateAgentTeamServiceType(string teamCode, string typeCode)
        {
            string strIns = "insert into Agent_Team_Service_Type(team_code, type_code) "
               + " values(@teamCode, @typeCode) ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("teamCode", DbType.String).Value = teamCode;
            p.Add("typeCode", DbType.String).Value = typeCode;

            template.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void DeteleAgentTeamServiceType(string teamCode, string typeCode)
        {
            IDbParameters para = template.CreateDbParameters();
            para.Add("teamCode", DbType.String).Value = teamCode;
            para.Add("typeCode", DbType.String).Value = typeCode;

            string strDel = " delete from Agent_Team_Service_Type where team_code=@teamCode and type_code=@typeCode ";
            template.ExecuteScalar(CommandType.Text, strDel, para);
        }

        #region private

        private static bool GetIsSystem(string strIsSystem)
        {
            if (strIsSystem.Equals("Y"))
                return true;

            return false;
        }

        private static string GetStrIsSystem(bool isSystem)
        {
            if (isSystem)
                return "Y";

            return "N";
        }

        private static TeamStatus GetStatus(string strStatus)
        {
            if (strStatus.Equals(TeamStatus.Active.ToString()))
                return TeamStatus.Active;

            return TeamStatus.Inactive;
        }

        #endregion
    }
}
